﻿using log4net;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Globalization;
using System.Text;

namespace DistributeTransaction.Utils
{
    public delegate void ReceivedDataEventHandler(SqlDataReader reader);

    public sealed class DBUtil
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(DBUtil));

        private static int TimeOut = 1200;

        public static bool ExecuteStoreProcedure(string sql, string ConnectString, params SqlParameter[] sqlParams)
        {
            if (!string.IsNullOrEmpty(sql))
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(ConnectString))
                    {
                        conn.Open();
                        using (SqlCommand cmd = new SqlCommand(sql))
                        {
                            cmd.Connection = conn;
                            cmd.CommandTimeout = TimeOut;
                            cmd.CommandType = CommandType.StoredProcedure;

                            if (sqlParams != null && sqlParams.Length > 0)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddRange(sqlParams);
                            }

                            cmd.ExecuteScalar();

                            cmd.Parameters.Clear();

                            return true;
                        }
                    }
                }
                catch (SqlException ex)
                {
                    log.ErrorFormat("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}",
                        sql, SQLParamsToString(sqlParams), ConnectString, ex);

                    throw ex;
                }
            }

            return false;
        }

        public static bool RunSQLCmd(string sql, string ConnectString, params SqlParameter[] sqlParams)
        {
            if (!string.IsNullOrEmpty(sql))
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(ConnectString))
                    {
                        conn.Open();
                        using (SqlCommand cmd = new SqlCommand(sql))
                        {
                            cmd.Connection = conn;
                            cmd.CommandTimeout = TimeOut;

                            if (sqlParams != null && sqlParams.Length > 0)
                            {
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddRange(sqlParams);
                            }

                            cmd.ExecuteNonQuery();

                            cmd.Parameters.Clear();

                            return true;
                        }
                    }
                }
                catch (SqlException ex)
                {
                    log.ErrorFormat("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}",
                        sql, SQLParamsToString(sqlParams), ConnectString, ex);

                    throw ex;
                }
            }

            return false;
        }

        public static DataTable FillDataTable(string sql, string ConnectString, params SqlParameter[] sqlParams)
        {
            DataTable dt = new DataTable();
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectString))
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandTimeout = TimeOut;

                    if (sqlParams != null && sqlParams.Length > 0)
                    {
                        //cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(sqlParams);
                    }

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                        return dt;
                    }
                }
            }
            catch (SqlException ex)
            {
                log.ErrorFormat("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}",
                    sql, SQLParamsToString(sqlParams), ConnectString, ex);

                throw ex;
            }
        }

        public static DataSet FillDataSet(string sql, string ConnectString, params SqlParameter[] sqlParams)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectString))
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.CommandTimeout = TimeOut;

                    if (sqlParams != null && sqlParams.Length > 0)
                    {
                        // cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddRange(sqlParams);
                    }

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                        cmd.Parameters.Clear();
                        return ds;
                    }
                }
            }
            catch (SqlException ex)
            {
                log.ErrorFormat("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}",
                    sql, SQLParamsToString(sqlParams), ConnectString, ex);

                throw ex;
            }
        }

        public static bool ExecuteReader(string sql, string ConnectString, ReceivedDataEventHandler readDataEvent, params SqlParameter[] sqlParams)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(ConnectString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        cmd.Connection = conn;
                        cmd.CommandTimeout = TimeOut;

                        if (sqlParams != null)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(sqlParams);
                        }

                        Stopwatch sw = new Stopwatch();
                        sw.Start();

                        using (SqlDataReader dataReader = cmd.ExecuteReader())
                        {
                            cmd.Parameters.Clear();
                            if (readDataEvent != null)
                            {
                                try
                                {
                                    readDataEvent(dataReader);
                                }
                                catch (Exception)
                                {
                                    throw;
                                }
                            }
                            dataReader.Close();
                        }

                        sw.Stop();

                        Trace.TraceInformation(string.Format("SQL Command Executed: {0}\r\nDuration: {1}", sql, sw.Elapsed));

                    }
                }
            }
            catch (SqlException ex)
            {
                log.ErrorFormat("Error while executing SQL: \r\n{0} \r\nParams:{1} at \r\n{2}", 
                    sql, SQLParamsToString(sqlParams), ConnectString, ex);

                throw ex;
            }

            return true;
        }


        private static string SQLParamsToString(params SqlParameter[] sqlParams)
        {
            StringBuilder sb = new StringBuilder();
            if (sqlParams != null)
            {
                foreach (SqlParameter item in sqlParams)
                {
                    sb.AppendLine(
                        string.Format(
                        CultureInfo.InvariantCulture,
                        "Name:{0}, Value:{1}, Type:{2}", item.ParameterName, item.SqlValue, item.SqlDbType)
                        );
                }
            }

            return sb.ToString();
        }
    }
}
